package brain;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

/**
 * 
 * @author JRVamplew
 *
 */
public class QueryBuilder {

	private static Statement stmt;
	private static Connection newConnection = Connect.StartConnection();
	
	/**
	 * Get Table Column Number
	 * @return columnNumber
	 */
	public static int getTableColumnNumber(String tableName) {
		int columnNumber = 0;
		ResultSet rs = null;
		Connection newConnect = newConnection;
        try {
        	String SQL = "SELECT COUNT(*) FROM information_schema.columns WHERE table_name = '" + tableName + "'";
			stmt = newConnect.createStatement();
	        rs = stmt.executeQuery(SQL);
	        
	        while (rs.next()) {
	            columnNumber = new Integer(rs.getString(1));
	            //System.out.println(columnNumber);
	        }
        } catch (Exception e) {
        	e.printStackTrace();
        }
        return columnNumber;
	}
	
	/**
	 * Get Table Row Number
	 * @return rowNumber
	 */
	public static int getTableRowNumber(String tableName) {
		int rowNumber = 0;
		ResultSet rs = null;
		Connection newConnect = newConnection;
        try {
        	String SQL = "SELECT COUNT(*) FROM " +tableName;
			stmt = newConnect.createStatement();
	        rs = stmt.executeQuery(SQL);
	        
	        while (rs.next()) {
	        	rowNumber = new Integer(rs.getString(1));
	            //System.out.println(rowNumber);
	        }
        } catch (Exception e) {
        	e.printStackTrace();
        }
		return rowNumber;
	}
	
	/**
	 * Get Word
	 * @param searchWord
	 */
	public static ArrayList<ArrayList<String>> getWord(String searchWord) {
		ResultSet rs = null;
		Connection newConnect = newConnection;
		//String[][] returnWordArray = null;
		ArrayList<ArrayList<String>> returnWordArray = new ArrayList<ArrayList<String>>();
        try {
        	int colNumber = getTableColumnNumber("words");
        	ArrayList<String> list1 = new ArrayList<String>();
        	//returnWordArray = new String[2][colNumber];
        	String SQL = "SELECT * FROM words WHERE name = '" + searchWord + "'";
			stmt = newConnect.createStatement();
	        rs = stmt.executeQuery(SQL);
	        //[0][] = rows, [][i-1] = columns
	        while (rs.next()) {
	        	for(int i = 1;i<colNumber+1;i++) {
	        		String data = rs.getString(i);
	        		System.out.println("data: " + data);
	        		list1.add(data);
	        		//returnWordArray[0][i-1] = data;
	        	}
	        }
	        System.out.println("list1.size(): " + list1.size());
	        SQL = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.Columns where TABLE_NAME = 'words'";
	        stmt = newConnect.createStatement();
	        rs = stmt.executeQuery(SQL);
	        //int j = 0;
	        ArrayList<String> list2 = new ArrayList<String>();
	        while (rs.next()) {
	        	//[1][] = rows, [][j] = columns
        		String headings = rs.getString(1);
        		System.out.println("headings: " + headings);
        		list2.add(headings);
        		//returnWordArray[1][j] = headings;
        		//j++;
	        }
	        returnWordArray.add(list1);
	        returnWordArray.add(list2);
        } catch (Exception e) {
        	e.printStackTrace();
        }
        return returnWordArray;
	}
	
	/**
	 * Get Word Data
	 * @param searchWord
	 */
	public static ArrayList<ArrayList<String>> getWordData(String searchWord) {
		ResultSet rs = null;
		Connection newConnect = newConnection;
		//String[][] returnWordDataArray = null;
		ArrayList<ArrayList<String>> returnWordDataArray = new ArrayList<ArrayList<String>>();
        try {
        	String SQL = "SELECT detailID FROM words WHERE name = '" + searchWord + "'";
			stmt = newConnect.createStatement();
	        rs = stmt.executeQuery(SQL);
	        String detailID = "";
	        while (rs.next()) {
	        	detailID = rs.getString(1);
	            //System.out.println(detailID);
	        }
	        if(!detailID.toString().equalsIgnoreCase("")) {
	        	String tableName = searchWord + detailID;
	        	int colNumber = getTableColumnNumber(tableName);
	        	//returnWordDataArray = new String[2][colNumber];
	        	ArrayList<String> list1 = new ArrayList<String>();
	        	SQL = "SELECT * FROM " + tableName;
	        	System.out.println("SQL: " + SQL);
	        	stmt = newConnect.createStatement();
	        	rs = stmt.executeQuery(SQL);
	        	while (rs.next()) {
	        		for(int i=1;i<colNumber+1;i++) {
	        			//[0][] = rows, [][i] = columns
	        			String data = rs.getString(i);
	        			System.out.println("extraData: " + data);
	        			//returnWordDataArray[0][i-1] = data;
	        			list1.add(data);
	        		}
	        		System.out.println();
		        }
	        	SQL = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.Columns where TABLE_NAME = '" + tableName + "'";
		        stmt = newConnect.createStatement();
		        rs = stmt.executeQuery(SQL);
		        //int j = 0;
		        ArrayList<String> list2 = new ArrayList<String>();
		        while (rs.next()) {
		        	//[1][] = headings, [][j] = columns
	        		String headings = rs.getString(1);
	        		System.out.println("headings: " + headings);
	        		//returnWordDataArray[1][j] = headings;
	        		list2.add(headings);
	        		//j++;
		        }
		        returnWordDataArray.add(list1);
		        returnWordDataArray.add(list2);
	        }
        } catch (Exception e) {
        	e.printStackTrace();
        }
        return returnWordDataArray;
	}
	
	/**
	 * Create New Table
	 * @param tableName
	 * @param idName Name of primary key
	 */
	public static void createNewTable(String tableName, String idName) {
		Connection newConnect = newConnection;
        try {
        	String SQL = "CREATE TABLE " + tableName + " (" + idName + " int not null auto_increment primary key);";
			stmt = newConnect.createStatement();
	        stmt.executeUpdate(SQL);
	        System.out.println("Success: New Table");
        } catch (Exception e) {
        	e.printStackTrace();
        }
	}
	
	/**
	 * Create New Column
	 * @param tableName
	 * @param columnName
	 * @param dataType (int/double/varchar(100))
	 */
	public static void createNewColumn(String tableName, String columnName, String dataType) {
		Connection newConnect = newConnection;
        try {
        	String SQL = "ALTER TABLE " + tableName + " ADD " + columnName + " " + dataType;
			stmt = newConnect.createStatement();
			stmt.executeUpdate(SQL);
	        System.out.println("Success: New Column");
        } catch (Exception e) {
        	e.printStackTrace();
        }
	}
	
	/**
	 * Create New Record
	 * @param tableName
	 * @param dataArray
	 */
	public static void createNewRecord(String tableName, String[][] dataArray) {
		Connection newConnect = newConnection;
		//[i][] = columns, [][0] = headings, [][1] = data
        try {
        	String SQL = "INSERT INTO " + tableName + " (";
        	for(int i = 0; i<dataArray.length;i++) {
        		if(i == dataArray.length-1) {
        			SQL += dataArray[i][0] + ") ";
        		} else {
        			SQL += dataArray[i][0] + ",";
        		}
        	}
        	SQL += "VALUES (\"";
        	for(int i = 0; i<dataArray.length;i++) {
        		if(i == dataArray.length-1) {
        			SQL += dataArray[i][0] + "\");";
        		} else {
        			SQL += dataArray[i][1] + "\",\"";
        		}
        	}
        	System.out.println("SQL: " + SQL);
			stmt = newConnect.createStatement();
        	stmt.executeUpdate(SQL);
	        System.out.println("Success: New Record");
        } catch (Exception e) {
        	e.printStackTrace();
        }
	}
	
	
	/**
	 * Initialise Common Words
	 * @param popLimit
	 * @return
	 */
	public static ArrayList<ArrayList<String>> initialiseCommonWords(int popLimit) {
		//Pop limit default = 100
		//String[][] returnPopWordsArray = null;
		ArrayList<ArrayList<String>> returnPopWordsArray = new ArrayList<ArrayList<String>>();
		ResultSet rs = null;
		Connection newConnect = newConnection;
        try {
	        String SQL = "CREATE VIEW popView AS SELECT * FROM words ORDER BY popularity DESC";
	        stmt = newConnect.createStatement();
	        stmt.executeUpdate(SQL);
	        //stmt.executeQuery(SQL);
	        System.out.println("Success: New View");
	        
	        SQL = "SELECT wordID, name FROM popView LIMIT 0 , "+popLimit;
	        int colNumber = 2;
	        //returnPopWordsArray = new String[2][popLimit];
	        ArrayList<String> list1 = new ArrayList<String>();
	        ArrayList<String> list2 = new ArrayList<String>();
	        stmt = newConnect.createStatement();
	        rs = stmt.executeQuery(SQL);
	        //[0][] = columns, [][i] = rows
	        while (rs.next()) {
        		for(int i =1;i<colNumber;i++) {
        			String index = rs.getString(1);
        			//returnPopWordsArray[0][i] = index;
        			list1.add(index);
        			System.out.println("indices: " + index);
        			String words = rs.getString(2);
        			//returnPopWordsArray[1][i] = words;
        			list2.add(words);
        			System.out.println("words: " + words);
        		}
	        }
	        returnPopWordsArray.add(list1);
	        returnPopWordsArray.add(list2);
        } catch (Exception e) {
        	e.printStackTrace();
        }
        return returnPopWordsArray;
	}
	
	/**
	 * Get Common Words List
	 * @param startPosition
	 * @param wordLimit
	 * @return
	 */
	public static ArrayList<ArrayList<String>> getCommonWordsList(int startPosition, int wordLimit) {
		//String[][] returnPopWordsArray = null;
		ArrayList<ArrayList<String>> returnPopWordsArray = new ArrayList<ArrayList<String>>();
		ResultSet rs = null;
		Connection newConnect = newConnection;
        try {
	        String SQL = "SELECT wordID, name FROM popView LIMIT " + startPosition + " , " + wordLimit;
	        int colNumber = 2; //wordID, name
	        //int arrayLength = wordLimit - startPosition;
	        //returnPopWordsArray = new String[2][arrayLength];
	        ArrayList<String> list1 = new ArrayList<String>();
	        ArrayList<String> list2 = new ArrayList<String>();
	        stmt = newConnect.createStatement();
	        rs = stmt.executeQuery(SQL);
	        //[0][] = columns, [][i] = rows
	        while (rs.next()) {
        		for(int i =0;i<colNumber;i++) {
        			String index = rs.getString(1);
        			//returnPopWordsArray[0][i] = index;
        			list1.add(index);
        			System.out.println("indices: " + index);
        			String words = rs.getString(2);
        			//returnPopWordsArray[1][i] = words;
        			list2.add(words);
        			System.out.println("words: " + words);
        		}
	        }
	        returnPopWordsArray.add(list1); //indices
	        returnPopWordsArray.add(list2); //words
        } catch (Exception e) {
        	e.printStackTrace();
        }
        return returnPopWordsArray;
	}
	
	/**
	 * Get Phrase
	 * @param phraseText
	 * @return
	 */
	public static ArrayList<ArrayList<String>> getPhrase(String phraseText) {
		ResultSet rs = null;
		Connection newConnect = newConnection;
		//String[][] returnPhraseArray = null;
		ArrayList<ArrayList<String>> returnPhraseArray = new ArrayList<ArrayList<String>>();
        try {
        	int colNumber = getTableColumnNumber("phrases");
        	//returnPhraseArray = new String[2][colNumber];
        	ArrayList<String> list1 = new ArrayList<String>();
        	String SQL = "SELECT * FROM phrases WHERE text = '" + phraseText + "'";
			stmt = newConnect.createStatement();
	        rs = stmt.executeQuery(SQL);
	        while (rs.next()) {
	        	for(int i = 1;i<colNumber+1;i++) {
	        		String data = rs.getString(i);
	        		System.out.println("data: " + data);
	        		//returnPhraseArray[0][i-1] = data;
	        		list1.add(data);
	        	}
	        }
	        //Get Column Headings
	        SQL = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.Columns where TABLE_NAME = 'phrases'";
	        stmt = newConnect.createStatement();
	        rs = stmt.executeQuery(SQL);
	        //int j = 0;
	        ArrayList<String> list2 = new ArrayList<String>();
	        while (rs.next()) {
        		String headings = rs.getString(1);
        		System.out.println("headings: " + headings);
        		//returnPhraseArray[1][j] = headings;
        		list2.add(headings);
        		//j++;
	        }
	        returnPhraseArray.add(list1); //data
	        returnPhraseArray.add(list2); //headings
        } catch (Exception e) {
        	e.printStackTrace();
        }
        return returnPhraseArray;
	}
	
}
